import pandas as pd

# 显示所有列
pd.set_option('display.max_columns', None)
# 男成绩
df = pd.read_excel('./18级高一体测成绩汇总.xls', sheet_name='男')
# 女成绩
df2 = pd.read_excel('./18级高一体测成绩汇总.xls', sheet_name='女')
# 不标准
df_score_standard = pd.read_excel('./体侧成绩评分表.xls', header=[0, 1])

df['男1000米跑'] = df['男1000米跑'].map(lambda x: float(str(x).replace('\'', '.')))
print("转换完成后 男子成绩")
print(df.head())
df_score_standard.loc[:, [('男1000米跑', '成绩'), ('女800米跑', '成绩')]] = df_score_standard.loc[:,
                                                                  [('男1000米跑', '成绩'), ('女800米跑', '成绩')]].applymap(
    lambda x: float(str(x).replace('"', '').replace('\'', '.')))
print('转换完成后 标准')
print(df_score_standard.head())

df.iloc[:, 2:] = df.iloc[:, 2:].applymap(float)
df2.iloc[:, 2:] = df2.iloc[:, 2:].applymap(float)
df_score_standard = df_score_standard.applymap(float)


def conv2score(x, type):
    """
    :param x:  体测中成绩
    :param type:  体侧的项目
    :return:  分数
    """
    score = 0
    if type in ['男1000米跑', '男50米跑', '女800米跑', '女50米跑']:
        # 越小越好
        for i in range(len(df_score_standard)):
            if x <= df_score_standard[(type, '成绩')][i]:
                score = df_score_standard[(type, '分数')][i]
                break
    else:
        # 越大越好
        for i in range(len(df_score_standard)):
            if x >= df_score_standard[(type, '成绩')][i]:
                score = df_score_standard[(type, '分数')][i]
                break
    return score


df_score = df.iloc[:, :8].__deepcopy__()
df_score.columns = ['班级', '姓名', '男1000米跑分数', '男50米跑分数', '男跳远分数', '男体前屈分数', '男引体分数', '男肺活量分数']
for i in range(len(df)):
    for j in range(2, df.columns.size - 3):
        df_score.iloc[i, j] = conv2score(df.iloc[i, j], df.columns[j])
df = pd.concat([df, df_score.iloc[:, 2:]], axis=1)
df['BMI'] = (df['体重'] / df['身高'] / df['身高'] * 10000).round(2)
df = df.iloc[:, [0, 1, 2, 11, 3, 12, 4, 13, 5, 14, 6, 15, 7, 16, 8, 9, 10]]
print("男子分数： \n", df.head())

df_score = df2.iloc[:, :8].__deepcopy__()
df_score.columns = ['班级', '姓名', '女800米跑分数', '女50米跑分数', '女跳远分数', '女体前屈分数', '女仰卧分数', '女肺活量分数']
for i in range(len(df2)):
    for j in range(2, df2.columns.size - 3):
        df_score.iloc[i, j] = conv2score(df2.iloc[i, j], df2.columns[j])
df2 = pd.concat([df2, df_score.iloc[:, 2:]], axis=1)
df2['BMI'] = (df2['体重'] / df2['身高'] / df2['身高'] * 10000).round(2)
df2 = df2.iloc[:, [0, 1, 2, 11, 3, 12, 4, 13, 5, 14, 6, 15, 7, 16, 8, 9, 10]]
print("女子分数： \n", df2.head())
